﻿
CREATE proc dbo.GetStandorte 
	@StandortID int = null,
	@Plz	nchar(10) = null,
	@Ort	nvarchar(50) = null,
	@Strasse nvarchar(100) = null
as
begin
	set nocount on;
	declare @errorMessage nvarchar(400)

	begin try

	if not exists(select 1 from dbo.datStandorte st where Plz = @Plz and Ort = @Ort and Strasse = @Strasse) 
		and @Plz is not null
		and @Ort is not null
		and @Strasse is not null
	begin
		select @StandortID = coalesce(max(st.StandortID),0) + 1 from dbo.datStandorte st where st.plz = @Plz

		insert into dbo.datStandorte (StandortID, Plz, Ort, Strasse)
		values (@StandortID, @Plz, @Ort, @Strasse)
	end

	select	rtrim(st.Plz) + '-' + right('0000' + convert(varchar(4),st.StandortID), 4) 'StandortID',
			st.Plz,
			st.StandortID 'LfdNr',
			st.Ort,
			st.Strasse
	from	datStandorte st
	where	(st.StandortID = @StandortID
				or @StandortID is null)
			and (st.Plz = @Plz
				or @Plz is null)
			and (st.Ort = @Ort
				or @Ort is null)
			and (st.Strasse = @Strasse
				or @Strasse is null)
	order by st.Plz, st.StandortID

	end try
	begin catch
		set @errorMessage = ERROR_MESSAGE()
		goto ExitOnError
	end catch

	return 0;

	ExitOnError:
		raiserror(@errorMessage, 16,1)
		return 1;
end